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

 

[EGname]

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

its not exactly correct. I said that only for b the color is not important. For All other data in column A color is important.

 

[liveinhope]

2 possible alternative formula

FORMULA 1 - always try for a match on both colour and letter. if you cant find match on colour & letter look for a match on letter only =IFERROR(VLOOKUP(A1&B1,lookup_table!$D$1:$G$4,4,FALSE),VLOOKUP(A1,lookup_table!$E$1:$G$4,3,FALSE))


FORMULA 2 - if value in column A of main sheet is "B" look for a match in the lookup table on "letter only .. colour not important " =IF(A1="b",VLOOKUP(A1,lookup_table!$E$1:$G$4,3,FALSE),VLOOKUP(A1&B1,lookup_table!$D$1:$G$4,4,FALSE))

your original data both give the same result. I have added a new row where results are different

letter	colour	old formula	FORMULA1	FORMULA2
A	RED	a	A	A
B		b	B	B
B	GREEN	#N/A	B	B
B	BKUE	#N/A	B	B
C	WHITE	c	C	C
D	BLACK	d	D	D
D	RED	d	D	#N/A

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

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

In what follows everything is in the same sheet (it's an irrelevant issue where the look up table is located.).

Row\Col	A​	B​	C​	D​	E​	F​
1​	a	red	A	a	red	A
2​	b		B	b		B
3​	b	green	B	c	white	C
4​	b	blue	B	d	black	D
5​	c	white	C
6​	d	black	D
7​	e	yellow	#N/A​
8​	b	yellow	B

In C2 control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(IF($E$1:$E$4="",$D$1:$D$4&"|"&CHAR(42),$D$1:$D$4&"|"&$E$1:$E$4),A1&"|"&B1),$F$1:$F$4)

 

[EGname]

Livenport

Thank you so much

I used IF formula very similar to your. Below is exact formula to my data, I did not adjust the formula to the sample of tables I had in my first post. It works perfect!!!

IF(A7='Secondary Data'!$A$130,VLOOKUP('Data-Input'!A7&'Data-Input'!C7,'Secondary Data'!$C$130:$D$150,2,FALSE),VLOOKUP('Data-Input'!A7,'Secondary Data'!$A$117:$D$129,4,FALSE))

Thank you sooo much for your help!!

 

[EGname]

Alladin

this is very interesting approach. Let me investigate it for a bit and I will reply with what did I end up with.

Thank you a lot