Excel vlookup continued on other columns

user_14706390 0
Viewed 53

Picture of columns

I cannot append these rows because the list would get too large. I have every city, village etc in this sheet and its UN code. I want to Vlookup on an another sheet and match the UN code to POD(name of a place). How can I Match several columns?

I have tired:

=INDEX((codes!$B$2:$B$51112) * (codes!$F$2:$F$27403) * (codes!$L$2:$L$33880);MATCH(0;(codes!$C$2:$C$5112=E4)*(codes!$G$2:$G$27403=E4)*(codes!$M$2:$M$33880=E4);FALSE))

But I get #VALUE! error

Picture of place to vlookup

Here I have managed to lookup some city UN codes because they were in the first two coumns with POD and UN code. But how do I get the rest of columns?

vlookup excel-formula
2 Answers

You can partition a range using INDEX and array constants. You will also need to use XLOOKUP instead of HLOOKUP or VLOOKUP, because you need to do both.

=XLOOKUP("c",INDEX(A1:D2,,{1,3}),INDEX(A1:D2,,{2,4}))

Use one such array for the lookup value (all the rows, columns 1 and 3) and another for the return value (all the rows, columns 2 and 4).

enter image description here

user_43619990

Image of sheet

I found it easier to just directly Vlookup from the sheet where I had these cities. And do lookups for each of the columns and than just unite the columns with strings. But for some reason I couldn't find UN codes for all of the cities. I just filled them manually

user_147063900
Related