How to look up a value in a table from a different sheet?

[JenniferMurphy]

I need to look up a value in a table from a different sheet. This minisheet shows what I have tried that don't work. These formulas (F6:F8) are in the same sheet just so they would be in the minisheet.

20210322 Electric usage log 04-2018 to 02-2021.xlsx
	C	D	E	F
5	City	Population
6	Tokyo	37,435,191		#N/A
7	Delhi	29,399,141		#VALUE!
8	Shanghai	26,317,104		#VALUE!
9	Mexico City	21,671,908
10	New York City	8,622,357
11	Los Angeles	4,085,014
12	San Jose	1,036,242
Populations

Cell Formulas
Range	Formula
F6	F6	=XLOOKUP("Delhi",Populations[City],Populations[Population])
F7	F7	=XLOOKUP("delhi",Populations[City],Populations[@Population])
F8	F8	=XLOOKUP("delhi",Populations[City],2)

These formulas all get errors. The first gets a #N/A, the other two a #VALUE.

What am I doing wrong?

 

[JenniferMurphy]

And when I click on the MiniSheet button, xl2bb draws 15-20 lines, something like this:

I think the actual number is quite a few more, but you get the idea. They disappear in less than a second, so I can't get a screen shot.

 

[Fluff]

If you are getting a #N/A error the "Delhi" does not exist in the table.

 

[KenU]

Some of the cities have trailing spaces in the table. You could remove those by using the TRIM function in another area of your workbook and then paste the values back into your table or alter the formula shown in F6 as below.

=XLOOKUP("Delhi",TRIM(Populations[City]),Populations[Population])

Hope that helps.

Regards,
Ken

 

[JenniferMurphy]

Some of the cities have trailing spaces in the table. You could remove those by using the TRIM function in another area of your workbook and then paste the values back into your table or alter the formula shown in F6 as below.

=XLOOKUP("Delhi",TRIM(Populations[City]),Populations[Population])

Hope that helps.

Regards,
Ken

Good catch. That was the problem. Thanks. ?

 

[JenniferMurphy]

If you are getting a #N/A error the "Delhi" does not exist in the table.

Yep. I missed the trailing space that Ken noticed.

 

[Fluff]

Glad we could help & thanks for the feedback.