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 in Others by Kithuzzz
• 27,740 points
30 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 by narikkadan
• 51,240 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,660 points
1,035 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,660 points
18,340 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
• 51,240 points
152 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
• 51,240 points
143 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
• 51,240 points
112 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
• 51,240 points
77 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 in Others by narikkadan
• 51,240 points
55 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