Double lookup formula with second lookup table some data can be any

[EGname]

Hi,
I am looking for help with double vlookup formula for table:
ColumnA____ColumnB_____ColumnC
1____a______red
2____b______
3____b______green
4____b______blue
5____c______white
6____d______black

Lookup table:

ColumnD_____ColumnE______ColumnG
a____________red__________A
b____________ ___________B
c____________white_________C
d____________black_________D

formula: C1=vlookup(A1&B1,lookup tableD1:G4, 3,0)
My problem is:in lookup table E2 (for b in column D) can be any text or even blank (in top data table), and it still should return with B. I do not know how to join D and E if cell value (E2)can be anything.

Hope it makes sense what I am trying to explain.

Thanks a lot

 

[liveinhope]

Let's leave the problem of where the colours (column B in your main table and column E in your lookup table ) are blank aside for the moment

You have another problem

you are looking for the letter in column A and colour in column B combined from your "main" sheet . looking to find that in your lookup table eg on row 1 you are looking for "ared" in the lookup table

the vlookup only looks for a match on the first column of the lookup table ie column D and column D only has the "letter" part , the colours are in column E so you wont find any matches !

can you amend your lookup table to look like the one below ? (insert column D ... formula = E1&F1 ) ..

D.	E	F	G
ared	a	red	A
b	b		B
cwhite	c	white	C
dblack	d	black	D

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

(If you cant do that will need to use match and index instead)

 

[EGname]

Let's leave the problem of where the colours (column B in your main table and column E in your lookup table ) are blank aside for the moment

You have another problem

you are looking for the letter in column A and colour in column B combined from your "main" sheet . looking to find that in your lookup table eg on row 1 you are looking for "ared" in the lookup table

the vlookup only looks for a match on the first column of the lookup table ie column D and column D only has the "letter" part , the colours are in column E so you wont find any matches !

can you amend your lookup table to look like the one below ? (insert column D ... formula = E1&F1 ) ..

D.	E	F	G
ared	a	red	A
b	b		B
cwhite	c	white	C
dblack	d	black	D

<tbody>
</tbody>

(If you cant do that will need to use match and index instead)

Yes, of cause I can do it. I can have concatenated E and F columns in lookup table, its no problem. Or I can leave it without concatenating and use index(match).
I do not know what to do with b. in any of above versions.
b can have any color or being blank in top (first table) and it alway should return with B (lookup table column G)

 

[liveinhope]

Ok - 2 MORE questions for your
QUESTION 1 is "lookup_table" another sheet name or is it a named range ? .. here's my results using my vlookup formula

(in my formula lookup_table is a worksheet) =VLOOKUP(A1&B1,lookup_table!$D$1:$G$4,4,FALSE)

A	RED	a
B		b
B	GREEN	#N/A
B	BKUE	#N/A
C	WHITE	c
D	BLACK	d

<tbody>
</tbody>

QUESTION 2 .. for some rows there is an entry on "lookup_table" where column E "letter" on the lookup table matches column A "letter" on the main table and column F on lookup table ("colour") matches column B "colour" on the main table
(row 1 and row 2 both work. row 2 on your "main" table column A has letter "B" column B "colour" is blank. there is al row on the lookup table where "letter" = B and colour is blank .. so that line matches

but what do you what do you want to do for the rows where there isn't a match on both letter and colour. eg row 3 letter B , colour GREEN .. there is only row on the lookup table with letter B but the colour is blank

 

[Guru008]

Try this

=INDEX('LOOKUP TABLE'!D:G,MATCH(A1&B1,'LOOKUP TABLE'!G:G&'LOOKUP TABLE',0),3)

 

[EGname]

Thank you, Guru008

I think there is something missing in the formula (see ???? marks below)
=INDEX('LOOKUP TABLE'!D:G,MATCH(A1&B1,'LOOKUP TABLE'!G:G&'LOOKUP TABLE'?????,0),3)

 

[EGname]

Hi livenhope,

Thank you for your answer.

1. Yes, lookup table is on other sheet.
2. This is my main problem. The formula works only for b with "blanks" in Column B in the main tables (if I have blanks for b in the look up table). Since b can have ANY value in column B of the main table (green, blue, "blank" and potentially many others), but it always need to return with B (see column G in the main table). I do not know what to put in lookup table in column E for raw b. It can be ANY value. or how to adjust the formula, that formula always return with B for b regardless what is in column B/ raw 2,3,4. Below is answers I nee to receive with formula in column C

ColumnA____ColumnB_____ColumnC
1____a______red__________A
2____b______ ____________B
3____b______green_________B
4____b______blue__________B
5____c______white_________C
6____d______black_________D

 

[EGname]

its kind of two vlookups in one formula:
one is looking for value in Column A(main table) and matching in column D (in lookup table) and returning with G (in lookup table). This one only for b raw in lookup table.
The second vlookup is for looking for joined column A and column B (in the main table), look for match in joined column in lookup table( I dont have this column in my original post) and return with G. This one for all, but b

 

[EGname]

or may be something with IF? Like IF A1=b, then vlookup just for this situation. IF A1=everything, but b(range in lookuptable without b including), than another vlookup?

 

[liveinhope]

Based on your expected outcomes below (and the fact that your lookup table only has 4 entries) it looks to me like you only really need to match on the "letter"
and you don't care if "colour" in your main and lookup tables are the same or different.

If you only care about matching on letter then go back to your original lookup table (i.e. delete the extra column I suggested you insert) and use this formula =VLOOKUP(A1,lookup_table!$D$1:$G$4,3,FALSE)

ColumnA____ColumnB_____ColumnC
1____a______red__________A
2____b______ ____________B
3____b______green_________B
4____b______blue__________B
5____c______white_________C
6____d______black_________D