Forum Discussion
Vlookup with If And statement
This one has me stumped but excel is awesome and I feel like this should be possible. Thank you in advance for any help!
I'm not sure the best way to go about this, I'm thinking a vlookup with with nested if and statements between 2 excel spreadsheets. I have a slightly large spreadsheet with names that are repeating and events repeating thus the file first needs to determine the class so it pulls the right time. The data contains Classes (class A, B and C), Name, Event and Time.
Spreadsheet A is the times. Everyone's times are recorded on Spreadsheet A. Spreadsheet B is to track the times per participant on a different tab per participant.
In Spreadsheet A, column A is the participant number, column C is event name, column G is the time and column H is the class.
In spreadsheet B, (the formula will be in Spreadsheet B). First, I need the formula to look at the class listed in spreadsheet B Column C Row 4 (Class A), then the formula needs to look at column H in spreadsheet A for Class A. Then it needs to look for the rider number on spreadsheet B, Column C row 2 (1234), needs to search spreadsheet A column A for rider 1234, and last it needs to look for the event (Runs) in spreadsheet B, and searches spreadsheet A column C for runs. After it matches all 3 then it needs to return the time in column G. If it does not find all 3 matches, then it needs to leave it blank.
IE: Spreadsheet B, Class "A", Rider "1234", Event "Runs", if it meets all three criteria's then return the time in Column G.
8 Replies
VLOOKUP is awkward because you have three criteria rather than one key. Use XLOOKUP with Boolean multiplication. Assuming Spreadsheet A has participant in A, event in C, time in G, and class in H, the pattern is:
=XLOOKUP(1,(A!$A$2:$A$1000=$C$2)(A!$C$2:$C$1000=$C$3)(A!$H$2:$H$1000=$C$4),A!$G$2:$G$1000,"")
Replace C3 with the cell containing the required event. Each comparison returns TRUE or FALSE; multiplying them produces 1 only for a row matching rider, event, and class. The final empty string keeps the result blank when no match exists.
If more than one row can meet all three conditions, XLOOKUP returns only the first. In that case, use FILTER with the same three conditions to return every matching time. Also ensure participant numbers have the same data type on both sheets; a text value such as "0012" will not match numeric 12. Converting the ranges to Excel Tables will make the formula easier to read and include new records.
Concatenate first, then vlookup on the concatenation of the 3 columns.
If you're using Excel 365 or Excel 2021, you can use either too of these approaches.
My preference is the first one because it doesn't rely on concatenating text:
=IFERROR(
XLOOKUP(1,
(SheetA!$H$2:$H$999=$C$4)*
(SheetA!$A$2:$A$999=$C$2)*
(SheetA!$C$2:$C$999=D4),
SheetA!$G$2:$G$999),"")
- The formula returns the matching Time from column G on SheetA, or a blank if no match is found.
If you prefer the combined-key approach, this is the corrected version:
=XLOOKUP(
$C$4&"|"&$C$2&"|"&D4,
SheetA!$H$2:$H$999&"|"&SheetA!$A$2:$A$999&"|"&SheetA!$C$2:$C$999,
SheetA!$G$2:$G$999,"")
Both formulas will return the same result. I slightly prefer the first version because it's more robust (it avoids any possibility of concatenated values accidentally matching), but I think either is a good solution for this scenario.
Use INDEX + MATCH with multiple criteria:
=IFERROR(INDEX('Sheet A'!$G:$G,MATCH(1,('Sheet A'!$H:$H=$C$4)*('Sheet A'!$A:$A=$C$2)*('Sheet A'!$C:$C=$C$6),0)),"")
This matches class, rider number, and event, then returns the time from column G. If no match is found, it returns blank.
Thank you, I will try Xlookup as shown above! The event is entered in Spreadsheet B next to the Rider number, it would be in column A row 2. I do have to manually enter the rider number, event name and class into Spreadsheet B. Thankfully I can copy the event name and class, I just have to manually enter each rider number.
It sounds like you are creating a sheet for each rider.
You create 1 sheet and change the rider on that sheet (you could use Data Validation to create a drop down list).
You could also use pivot table or similar as I mentioned and then just select the rider.
If you have a sample workbook and more about what you need, have, want I'm sure we could get you to the finish line.
In Spreadsheet A, I created a concatenated helper column in column H using =A2 & B2 & C2.
Then I used XLOOKUP in Spreadsheet B, either:
=XLOOKUP(A2 & B2 & C2, A!$H:$H, A!$G:$G, ,0)
or create same helper formula in sheet2 and use
=XLOOKUP(H2, A!$H:$H, A!$G:$G,,0)
Sheet1
Sheet with Xlookup
I'm guessing you have other data manually entered in Spreadsheet B and that is why you can use a pivot table or power query (but actually this might still be an option)? If you must use a lookup you can combine criteria inside the xlookup something like the following. but note I don't see where the event is located in spreadsheet B. Also, this can easily be an array so a) I will assume the classes and events are an array of values in columns C4:.C99 and D4:.D99 but that the rider number is unique to that sheet (C2). So in G4 would be:
=XLOOKUP(C4.C99 & $C$2 & D4:.D99, SheetA!H1:.H99 & SheetA!A1:.A99 & SheetA!C1:.C99, SheetA!G1:.G99, "")
