IF and VLOOKUP

New Contributor

Hi,

I am working on a project where I am trying to populate a value from a table (that is being queried into the workbook from an external source). 

Ex: If I type in the State value as 'NY' - I need the cell beneath it to auto populate a value that is stored in a table/sheet in the same workbook. The table will be updated from time to time or will be linked to an external database/source. The value that's currently being shown as FALSE needs to be a number, such that when 'NY' is typed in cell described as state -the power $/kWh is auto populated.

 

=IF($B$22 = PR!$B2:$B52, TRUE, FALSE)

I know the above formula is incorrect.

 

Any suggestions would help

 

 

azmxmlk_0-1648580468696.png

 

3 Replies

@azmxmlk

Let's say that the state abbreviations are in B2:B52 on the PR sheet and the corresponding Power $/kWh in E2:E52.

The formula in B23 could be

=IFERROR(VLOOKUP(B22,PR!$B$2:$D$52,3,FALSE),"") 

(3 is the 3rd column of B2:D52)

or, if you have Microsoft 365 or Office 2021:

=XLOOKUP(B2,PR!$B$2:$B$52,PR!$D$2:$D$52,"")

So the state abbs are in B2:B52 - what's D52 doing here - I m confused - was it a typo?

@azmxmlk 

Yes, sorry, a typo.

"and the corresponding Power $/kWh in E2:E52." should have been "and the corresponding Power $/kWh in D2:D52.