XLOOKUP Formula Issue Horizontal vertical lookup

0 votes
I'm attempting to retrieve data with both vertical and horizontal lookups. The product that I'm attempting to match is provided by the vertical lookup, and the dates are provided by the horizontal lookup. I have two sheets open with this lookup in progress. It appears to be functioning OK, however, for some reason the lookup retrieves the value below the correct value. This is quite strange. Could someone please glance at the formula and let me know what might be wrong?

Forecast2 column A contains the data that would correspond with the lookup value $E59 ($E59). The date is on page $N$1, the date headers are in Forecast2's $G$4:$O$4 section, and the actual data is on page $G$5:$O$52. Right now, my formula performs the search, but it enters the value below the correct value rather than the correct value itself. As an illustration, $E59 corresponds to Row 5 on the Forecast2 sheet; yet, for some reason, it is sending me the value from Row 6. The algorithm then generates the value for row 7 if I adjust the value of $E59 to correspond with what row 6's value is.
Nov 5, 2022 in Others by Kithuzzz
• 29,080 points
87 views

1 answer to this question.

0 votes

Actually, your ranges are wrong. To correct it, you need to put your values in A5:A52 and then refer to A5:A52 in place of A4:A51. Hence, your formula would become

 =XLOOKUP($E59,Forecast2!$A$5:$A$52,XLOOKUP('Main Page'!N$1,Forecast2!$G$4:$O$4,Forecast2!$G$5:$O$52))
answered Nov 5, 2022 by narikkadan
• 53,920 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Vlookup error when lookup value cell has been populated with formula

Your formula returns 4 characters. =LEFT(M8,FIND(" ",M8)-1) will return ...READ MORE

answered Jan 22 in Others by narikkadan
• 53,920 points
38 views
0 votes
1 answer

Events & Threading in Windows Forms

I don't think you should be updating ...READ MORE

answered Nov 10, 2018 in Others by nirvana
• 3,130 points
379 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
533 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
482 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,503 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,017 views
0 votes
1 answer
0 votes
1 answer

Excel Formula to convert from cm to inches and feet & round off to two digits

Use the TEXT function to format, and ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 53,920 points
125 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP