"LOOKUP" formula issues

[Candy Shakes]

Hi,

I've developed a formula which looks for the LAST/FINAL value in an excel range that is equal to 1! I've used LOOKUP instead of MATCH as I am trying to find the final value, not the first. Here is my current formula:

=LOOKUP(2,1/([VALUE RANGE I AM SEARCHING IN]=1),[FINAL VALUE DISPLAYED IN CELL])

Basically, if row B contains 20 numbers (and includes seven "1" values), it will search for the final value that displayed the number "1". The cell would then display the date above in row A from the last number "1" found.

However I've gone wrong somewhere with the formula... The "Final Value Displayed in Cell" will display the 'header' of a certain range in a table I am using to collect data. I think the issue is with the first part ("Value Range I Am Searching In"). I have 40 different names to lookup in this data. There are 40 rows to lookup from in the other table. The formula works if I select one row of data to search from, however this doesn't search for the name of the person, but just displays each row one by one as I auto fill the formula (if that makes sense).

I'm struggling to fix this formula to look up the name of each person in Column A. And find their last "1" in the spreadsheet.

[AliGW]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AliGW]

Very difficult to visualise your data without a workbook, but in any case I cannot see how your proposed formula is referencing a names column.

It's not completely clear what you are after here, which is why I've requested a sample workbook (which should be desensitised). Are you able to provide this?

[Candy Shakes]

Capture.JPG

I have attached an excel sheet to this post.

In cell B3, it should:
1) Find John Smith in the data table
2) Lookup the last time they finished first

As you can see, the data table has the names scrambled in different order. So I'm not sure how to include in the formula, an area that will search for John Smith. At the moment it is showing that Sam Michael last achieved 1st place in Game 9, but that is next to John Smith's name.

[AliGW]

Try this in B3 copied down:

=IFERROR(LOOKUP(2,1/(OFFSET($E$2:$N$2,MATCH(A3,$D$3:$D$7,0),)=1),$E$2:$N$2),"-")

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name			DATA TABLE
2		Last time 1st place achieved			Game 1	Game 2	Game 3	Game 4	Game 5	Game 6	Game 7	Game 8	Game 9	Game 10
3	John Smith	-		Sam Michael	1	1	1	3	2	1	1	1	1	2
4	Sam Michael	Game 9		Bob Parker	2	3	2	2	1	3	2	3	2	1
5	David Jones	-		John Smith	3	2	4	5	3	2	5	5	3	4
6	Bob Parker	Game 10		Tony Anderson	5	5	5	1	4	4	4	4	4	3
7	Tony Anderson	Game 4		David Jones	4	4	3	4	5	5	3	2	5	5

Sheet: Sheet1

[Candy Shakes]

Thanks very much for your quick response!

So it's not working entirely on my spreadsheet. The results are not accurate when doing a human 'eye test' and when I check the data manually.

I'm thinking... In my spreadsheet, the names are listed in column D as shown above. However there are 20 other columns of data between the names in column D, and the first game listed. Will this require the formula to be adjusted?

[AliGW]

I don't understand what you mean. The results in my table are correct, aren't they?

=IFERROR(LOOKUP(2,1/(OFFSET($E$2:$N$2,MATCH(A3,$D$3:$D$7,0),)=1),$E$2:$N$2),"-")

$E$2:$N$2 - cells containing game names.

$D$3:$D$7 - range in lookup table containing names.

[Candy Shakes]

Yes, but when I plug the formula into my personal spreadsheet, the results are inaccurate for some reason. I've also checked the formula to make sure I haven't made any errors when plugging it into my sheet.

As I said, I have 40 names in my spreadsheet. There are 20 columns after these names with random information. From column 21 to 30 are "Game 1 to Game 10". If you don't think that's causing the issue, then ignore this.

[AliGW]

It probably is causing the issue.

Have you tried this?

=IFERROR(LOOKUP(2,1/(OFFSET($U$2:$AD$2,MATCH(A3,$D$3:$D$42,0),)=1),$U$2:$AD$2),"-")

I am assuming that the lookup names are still in D and the result names in A.

If this doesn't work then tell me the cell where:

1. The first result name is (in the original formula this is A3).
2. The first result name is (in the original formula this is D3).
3. The phrase Game 1 is (in the original formula this is E2).

[ABV20.18]

Welcome to the Lookup troubleshooting club Candy Shakes :-)

You'll see in my thread that Lookup is giving the wrong result sometimes.....yet it's working for AliGW !

Have you done evaluate formula on any of your odd results and seen whether you too are getting the correct result in Evaluate Formula but the wrong result in the cell?

[AliGW]

ABV20.18 - there is no indication whatsoever that this is the same issue as yours. Candy just needs to get the ranges right here.

Let's not cloud the issue, please - it's not even the same version of Excel.

[Candy Shakes]

Okay so I just figured out the issue...

=IFERROR(LOOKUP(2,1/(OFFSET($E$2:$N$2,MATCH(A3,$D$3:$D$7,0),)=1),$E$2:$N$2),"-")

The area I highlighted in blue above is where I went wrong. I selected D:D thinking this wouldn't cause an issue. Instead I adjusted it to select the entire column that is ONLY listed in my table.

And now... IT WORKS!

Thank you very much AliGW, and I love you!

Saved me hours of frustration not knowing what was wrong...

[AliGW]

You are welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.