VLookup to return the last value of a row based on criteria

[tarek]

Hi,

I've been researching below issue for couple of days with out any luck of a solution:

I am trying to create a formula which return the last value in a row based on a client name example:

Column A (client name).......Column B (Data1).......Column C (Data2).............Column D (Data3).......
ClientA....................................6...............................2.......................................4....................
ClientB.................................................................... 3
ClientC..............................................................................................................5...................
ClientD
ClientE


Formula should return:
ClientA: 4
ClientB: 3
ClientD: 5
and so on...

Please note: in my case I may have 26 columns of data (Hence: this is why I couldn't use a nested if). I tried leveraging =INDEX(Range,MATCH(9.99999999999999E+307,Range)) but it that didn't work as the results is depended on the client name users pick from a drop down.

Any help on this is much appreciated!

Tarek

 

[anglais428]

=LOOKUP(9.99E+307,A2:Z2) will give you the right most numeric value in a range.

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Client​

[/td][td]

Data1​

[/td][td]

Data2​

[/td][td]

Data3​

[/td][/tr]

[tr][td]

2​

[/td][td]

ClientA​

[/td][td]

6​

[/td][td]

2​

[/td][td]

4​

[/td][/tr]

[tr][td]

3​

[/td][td]

ClientB​

[/td][td] [/td][td]

3​

[/td][td] [/td][/tr]

[tr][td]

4​

[/td][td]

ClientC​

[/td][td] [/td][td]

​

[/td][td]

5​

[/td][/tr]

[tr][td]

5​

[/td][td]

ClientD​

[/td][td]

1​

[/td][td] [/td][td]

4​

[/td][/tr]

[tr][td]

6​

[/td][td]

ClientE​

[/td][td] [/td][td]

3​

[/td][td]

7​

[/td][/tr]

[tr][td]

7​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

8​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

9​

[/td][td]

ClientA​

[/td][td]

4​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

10​

[/td][td]

ClientB​

[/td][td]

3​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

11​

[/td][td]

ClientC​

[/td][td]

5​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

12​

[/td][td]

ClientD​

[/td][td]

4​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

13​

[/td][td]

ClientE​

[/td][td]

7​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

14​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in B9 copied down
=LOOKUP(9.99E+307,INDEX($B$2:$D$6,MATCH(A9,$A$2:$A$6,0),0))

Hope this helps

M.

 

[tarek]

Thank you Anglais and Marcelo for your time and prompt response. We are on the right track, and apologies if I didn't translate my thoughts accurately. Looking at Marcelo's table, the scenario that I have is let us say that I only have a drop down list of clients name in cell A10, how can I have the formula reference A10 and bring the correct data based on user client selection from the list?

Thanks again for all the help on this.

 

[Marcelo Branco]

Thank you Anglais and Marcelo for your time and prompt response. We are on the right track, and apologies if I didn't translate my thoughts accurately. Looking at Marcelo's table, the scenario that I have is let us say that I only have a drop down list of clients name in cell A10, how can I have the formula reference A10 and bring the correct data based on user client selection from the list?

Thanks again for all the help on this.

Try

=LOOKUP(9.99E+307,INDEX($B$2:$D$6,MATCH(A10,$A$2:$A$6,0),0))

M.

 

[tarek]

Great (Que Otimo)! Thank you Marcelo
This resolved my issue.

 

[Patrik_T]

Great Info, but does someone know how you could do the same thing but with text instead of numbers?

Much appreciated

 

[Marcelo Branco]

Great Info, but does someone know how you could do the same thing but with text instead of numbers?

Much appreciated

Welcome to Mr Excel

Try this
=LOOKUP(REPT("z",255),INDEX($B$2:$D$6,MATCH(A9,$A$2:$A$6,0),0))

Hope this helps

M.

 

[Patrik_T]

Welcome to Mr Excel

Try this
=LOOKUP(REPT("z",255),INDEX($B$2:$D$6,MATCH(A9,$A$2:$A$6,0),0))

Hope this helps

M.

Thank you very much! This worked great