vlookup based on if function

[kypok999]

hi Guys,

I have data range I need to lookup for:

I attached the file so I am looking for unique id as in "Combined Sheet" column A2 I need to produce result in column x,y,z but need to be based on x1 y1 z1 values so its looking for A2 in sheet "Top3FAVData" if x1 =1 for x column and if y1=1 and z1=1 for y ,z columns so bascially lookup should look for A2+$x1 from "Combined sheet" in "Top3FAVData" it would look into A2 column +z2 and show result from AE column

I copy pasted result in x1,y2,z2 how it should look like with formula

Thank you in advance sorry if I wrote this complicated..

https://docs.google.com/spreadsheets/d/110-z6_x0U14iHWS5kAWjSg1S4-USAZ8vXE-AIxrLaXc/edit?usp=sharing

 

[inactiveuserps07]

@kypok999, here is what I would recommend:

Combined!X2 formula:

=INDEX(TOP3FAVData!$AE:$AE, MATCH(A2,TOP3FAVData!$A:$A,0))

Combined!Y2 formula:

=INDEX(TOP3FAVData!$AE:$AE, MATCH(A2,TOP3FAVData!$A:$A,0)+1)

Combined!Z2 formula:

=INDEX(TOP3FAVData!$AE:$AE, MATCH(A2,TOP3FAVData!$A:$A,0)+2)

Of course, I can't test that on your sheet itself. But I believe those should work for what you're trying to do.

 

[kypok999]

ErikTyler thank you!!!

 

[inactiveuserps07]

@kypok999, glad to help.

By the way, if there will be times when your ID in Combined! may not be found in the second sheet, you may want to wrap each formula I provided in an IFERROR(). For instance:

=IFERROR(INDEX(TOP3FAVData!$AE:$AE, MATCH(A2,TOP3FAVData!$A:$A,0)),"NOT FOUND")

 

[kypok999]

Thank you I have one more formula I need..

Again based on A2 in "Combined" i want to display whats in Column O2 in "TOP3FAVDATA" which can be either 1 or 0 and only if its is 1 in Column O I want to display result from Column Z I attached example in Column AA where formula should be

Many thanks https://docs.google.com/spreadsheets/d/1XUyg_X8EtfDnqQl5ylwSk5MaFoMFxiofSC_zLxiO6nk/edit?usp=sharing

 

[inactiveuserps07]

@kypok999, again, it's difficult to write formulas without being able to test them. But see if this works in Combined!AA2 (and then drag-copied down as needed):

=IF(SUMPRODUCT((TOP3FAVData!A:A=$A2)*(TOP3FAVData!O:O=1))=0,"",SUMPRODUCT((TOP3FAVData!A:A=$A2)*(TOP3FAVData!O:O=1)*(TOP3FAVData!Z:Z)))

There are places in your TOP3FAVData! list where no match will be found for that ID in A:A with a 1 in O:O (i.e., O:O is all zeros for that ID). In that case, the above formula will leave Combined!AA:AA blank.

 

[kypok999]

Hi Erik can you not download fike i attached?

Thanks Alex

 

[inactiveuserps07]

Alex, I don't download spreadsheets from the Internet for security purposes.

Did you try the formula I provided for you? I don't see it in the spreadsheet.

 

[kypok999]

Hi Erik it coms back with #value error

 

[inactiveuserps07]

Alex, try this in AA2 and copied down:

=IF(SUMPRODUCT((TOP3FAVData!A:A=$A2)*(TOP3FAVData!O:O=1))=0,"",INDEX(TOP3FAVData!Z:Z, SUMPRODUCT((TOP3FAVData!A:A=$A2)*(TOP3FAVData!O:O=1)*ROW(TOP3FAVData!O:O))))