Vlookup error when lookup value cell has been populated with formula

0 votes

I have the following formula which is producing a #N/A error:

=VLOOKUP(N8,Lookups!J5:L2772,3,FALSE)

The formula in N8 is:

=LEFT(M8,4)

And the value in N8 will be the first part of a UK postcode (e.g. N1, W1A, SW1Y, TF1, SO26)

I've tried changing the vlookup to an index and match formula but cannot get that to work either.

formulas

values

postcode lookup

Jan 22, 2023 in Others by Kithuzzz
• 38,020 points
475 views

1 answer to this question.

0 votes

Your formula returns 4 characters. =LEFT(M8,FIND(" ",M8)-1) will return the characters up to the first space (and excluding the space).

TRIM(N8) would also remove trailing spaces if you plan to keep your formula as it was. In that case =VLOOKUP(TRIM(N8),Lookups!J5:L2772,3,FALSE) would also work, but this is less likely to throw an error:

(=VLOOKUP(LEFT(M8,FIND(" ",M8)-1),'Lookups'!J5:L2772,3,0))
answered Jan 22, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

TCPDF ERROR: Some data has already been output, can't send PDF file

To answer your question, do add the ...READ MORE

answered Feb 16, 2022 in Others by Aditya
• 7,680 points
4,136 views
0 votes
1 answer

Web API Error - This request has been blocked; the content must be served over HTTPS

 If your web app is being hosted ...READ MORE

answered Feb 16, 2022 in Others by Aditya
• 7,680 points
35,283 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,720 points
946 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,720 points
891 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,720 points
661 views
0 votes
1 answer

Convert three letter country codes to full country names

Just create a list to be used in ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,720 points
990 views
0 votes
1 answer
0 votes
1 answer

How to sum the value of 2 rows with vlookup by only using 1 formula?

 Try in Excel Online: • Formula used in cell C3 =SUM(SCAN(0,M3:N3,LAMBDA(x,y,VLOOKUP(y,P3:Q12,2,0)))) Works ...READ MORE

answered Jan 17, 2023 in Others by narikkadan
• 63,720 points
458 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